package com.hulu.yec.stu.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.hulu.yec.stu.entity.StuUserEntity;
import com.hulu.yec.common.Constant;

public class StuUserDao {
	public List<StuUserEntity> list(String id) throws SQLException, ParseException {
		//数据库查询语句
		StringBuilder sb = new StringBuilder();
		//参数集合
		List<Object> paramList = new ArrayList<Object>();
		sb.append("SELECT n.exam_time_start, ");
		sb.append("n.exam_time_stop, ");
		sb.append("n.exam_classroom, ");
		sb.append("n.to_user_ids, ");
		sb.append("n.exam_paper_id,");
		sb.append("n.to_class_id, ");
		sb.append("p.name AS 'qes_exam_paper.name', ");
		sb.append("u.name AS 'sys_user.name', ");
		sb.append("n.operate_time ");
		sb.append(",p.state ");
		sb.append(" FROM sys_user u,exam_plan n ,qes_exam_paper p ");
		sb.append(",sys_user su ");
		sb.append(" WHERE n.exam_paper_id=p.id AND n.operate_user_id = u.id ");
		sb.append(" AND(n.to_class_id = su.class_id OR n.to_user_ids LIKE '%,"+id+",%') ");
		sb.append("AND su.id="+id);
		sb.append(" ORDER BY n.id desc ");


		//链接数据库
		try {
			Class.forName(Constant.DRIVER);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<StuUserEntity> stulist = new ArrayList<>();
		
		try {
			connection = DriverManager.getConnection(Constant.URL, Constant.USER, Constant.PASSWORD);
			preparedStatement = connection.prepareStatement(sb.toString());
			if(paramList != null){
				for(int i = 0; i < paramList.size(); i++){
					preparedStatement.setObject(i+1,paramList.get(i));
				}
			}
			resultSet = preparedStatement.executeQuery();
			while(resultSet.next()){
				StuUserEntity stuUserEntity1 = new StuUserEntity();
				stuUserEntity1.setExamTimeStart(resultSet.getTimestamp("exam_time_start"));
				stuUserEntity1.setExamTimeStop(resultSet.getTimestamp("exam_time_stop"));
				stuUserEntity1.setExamClassRoom(resultSet.getString("exam_classroom"));
				stuUserEntity1.setToUserIds(resultSet.getString("to_user_ids"));
				stuUserEntity1.setToClassId(resultSet.getInt("to_class_id"));
				stuUserEntity1.setExamPaperId(resultSet.getInt("exam_paper_id"));
				stuUserEntity1.setPaperName(resultSet.getString("qes_exam_paper.name"));
				stuUserEntity1.setUserName(resultSet.getString("sys_user.name"));
				stuUserEntity1.setOperateTime(resultSet.getTimestamp("operate_time"));
				// 判断并加入state
				//DateFormat df = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
				/*String startTime = resultSet.getString("exam_time_start");
				String stopTime = resultSet.getString("exam_time_stop");
				String operateTime = resultSet.getString("operate_time");*/
				Date dt1 = resultSet.getTimestamp("exam_time_start");
		        Date dt2 = resultSet.getTimestamp("exam_time_stop");
		        Date dt3 = new Date();
		        int state  = 0;
		        if((resultSet.getInt("state")==1)&&dt1.getTime()<dt3.getTime()&&dt3.getTime()<dt2.getTime()){
		        	state = 1;
				}else{
					state  = 2;
				}
		        stuUserEntity1.setState(state);
		       // System.out.println(state + "...");
				stulist.add(stuUserEntity1);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			if(resultSet != null){
				resultSet.close();
			}
			if(preparedStatement != null){
				preparedStatement.close();
			}
			if(connection != null)
				connection.close();
		}
		return stulist;
		

		}
}
